<?php
class M_rekap extends Nvl_Model{
	function __construct(){
		parent::__construct();
		$this->table_name='m_rekap';
		$this->column_primary='rekap_id';
		$this->column_order='';
		$this->column_unique='rekap_nama';
		$this->column_active='rekap_aktif';
	}
	
	function get_list($start, $end, $search_text){
		if($search_text != ''){
			$this->db->like('rekap_nama', $search_text);
		}
		if($start != 0 && $end != 0){
			$this->db->limit($end, $start);
		}
		$record = $this->db->get('m_rekap');
		$final[] = array(
			"total"=>$record->num_rows(),
			"results"=>$record->result()
		);
		return json_encode($final);
	}
	
	function export_rekap($form_data, $grouping){
		$this->db->select($grouping . ' AS data, count(prestasi_id) AS total_prestasi, sum(prestasi_skor) AS total_skor');
		$this->db->group_by($grouping)->order_by('total_prestasi', 'desc')->order_by('total_skor', 'desc');
		$this->db->where('prestasi_valid', 1);
		if($grouping == 'siswa_kelas'){
			$this->db->join('m_siswa','t_prestasi_det.det_prestasi_siswa_id = m_siswa.siswa_nis')
					 ->join('t_prestasi', 't_prestasi_det.det_prestasi_prestasi_id = t_prestasi.prestasi_id');
			$result = $this->db->get('t_prestasi_det');
		}else{
			$this->db->join('m_sekolah', 't_prestasi.prestasi_sekolah_id = m_sekolah.sekolah_id');
			$result = $this->db->get('t_prestasi');
		}
		return $result->result();
	}
	
	function export_rekap_detail($form_data){
		extract(get_object_vars($form_data));
		$sql = "
			SELECT
				siswa_nis,
				siswa_kelas,
				siswa_nama,
				level_nama,
				juara_nama,
				lomba_judul,
				prestasi_skor,
				sekolah_nama
			FROM t_prestasi_det
			JOIN m_siswa ON m_siswa.siswa_nis = t_prestasi_det.det_prestasi_siswa_id
			JOIN t_prestasi ON t_prestasi.prestasi_id = t_prestasi_det.det_prestasi_prestasi_id
			JOIN t_lomba ON t_lomba.lomba_id = t_prestasi.prestasi_lomba_id
			JOIN m_juara ON t_prestasi.prestasi_juara_id = m_juara.juara_id
			JOIN m_level ON m_level.level_id = t_lomba.lomba_id
			JOIN m_sekolah ON t_prestasi.prestasi_sekolah_id = m_sekolah.sekolah_id
			WHERE prestasi_valid = 1
			AND prestasi_tanggal >= '". help_en_date($rekap_tanggal) ."'
			AND prestasi_tanggal <= '". help_en_date($rekap_tanggal_akhir) ."'
		";
		if(help_numeric($rekap_sekolah_id) != 0){
			$sql .= "AND sekolah_id = ". $rekap_sekolah_id;
		}
		if(help_numeric($rekap_level_id) != 0){
			$sql .= "AND lomba_level_id = ". $rekap_level_id;
		}
		$result = $this->db->query($sql);
		return $result->result();
	}
	
	function get_list_sekolah($start, $end, $search_text){
		$sql = "
			SELECT 0 as sekolah_id, 'Semua' AS sekolah_nama
				UNION 
			SELECT sekolah_id, sekolah_nama FROM m_sekolah 
		";
		$record = $this->db->query($sql, FALSE);
		$final[] = array(
			"total"=>$record->num_rows(),
			"results"=>$record->result()
		);
		return json_encode($final);
	}
}